package com.aibaixun.web.controller.common;

import cn.smallbun.screw.core.Configuration;
import cn.smallbun.screw.core.engine.EngineConfig;
import cn.smallbun.screw.core.engine.EngineFileType;
import cn.smallbun.screw.core.engine.EngineTemplateType;
import cn.smallbun.screw.core.execute.DocumentationExecute;
import cn.smallbun.screw.core.process.ProcessConfig;
import com.aibaixun.common.core.domain.R;
import com.aibaixun.system.domain.DicmDevice;
import com.aibaixun.system.domain.DicmNvr;
import com.aibaixun.system.domain.dto.DicmNvrRegisterDto;
import com.aibaixun.system.service.DicmDeviceService;
import com.aibaixun.system.service.DicmNvrService;
import com.aibaixun.system.service.EvsApiService;
import com.alibaba.fastjson.JSONObject;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.web.bind.annotation.*;

import javax.sql.DataSource;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.*;

/**
 * 公共模块
 *
 * @author zhangyh
 * @date 2023-01-16
 **/
@RestController
@RequestMapping("/common" )
public class CommonController {

    @Autowired
    ApplicationContext applicationContext;

    @Autowired
    private DicmDeviceService dicmDeviceService;

    @Autowired
    private DicmNvrService dicmNvrService;

    @Autowired
    private EvsApiService evsApiService;


    private static final String URL = "jdbc:postgresql://8.138.118.50:16001/itcdispatch";
    private static final String USER = "vbadmin";
    private static final String PASSWORD = "Vbase@1234";

    @GetMapping("/pgsql/file")
    public R pgsqlFile() throws Exception {
        // Step 1: Create HikariConfig object and set properties
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(URL);
        config.setUsername(USER);
        config.setPassword(PASSWORD);
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

        // Step 2: Create HikariDataSource object using the configuration
        HikariDataSource dataSource = new HikariDataSource(config);

        try (Connection connection = dataSource.getConnection()) {
            // Step 3: Get database metadata
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(null, "public", "%", new String[]{"TABLE"});

            // Step 4: Create a Word document
            XWPFDocument document = new XWPFDocument();
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                XWPFParagraph paragraph = document.createParagraph();
                XWPFRun run = paragraph.createRun();
                run.setText("Table: " + tableName);
                run.setBold(true);
                run.setFontSize(16);

                ResultSet columns = metaData.getColumns(null, null, tableName, "%");
                XWPFTable table = document.createTable();
                XWPFTableRow headerRow = table.createRow();
                headerRow.createCell();
                headerRow.getCell(0).setText("Column Name");
                headerRow.createCell();
                headerRow.getCell(1).setText("Type");
                headerRow.createCell();
                headerRow.getCell(2).setText("Nullable");
                headerRow.createCell();
                headerRow.getCell(3).setText("Default");

                while (columns.next()) {
                    XWPFTableRow row = table.createRow();
                    row.createCell();
                    row.getCell(0).setText(columns.getString("COLUMN_NAME"));
                    row.createCell();
                    row.getCell(1).setText(columns.getString("TYPE_NAME"));
                    row.createCell();
                    row.getCell(2).setText(columns.getString("IS_NULLABLE"));
                    row.createCell();
                    row.getCell(3).setText(columns.getString("COLUMN_DEF"));
                }
            }

            // Step 5: Write the document to a file
            try (FileOutputStream out = new FileOutputStream("E:\\pgsql.docx")) {
                document.write(out);
            }
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            if (dataSource != null) {
                dataSource.close();
            }
        }
        return R.ok();
    }

    public static void main(String[] args) {
//        HikariConfig hikariConfig = new HikariConfig();
////        hikariConfig.setDriverClassName("com.mysql.cj.jdbc.Driver");
////        hikariConfig.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/guian_storage?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&autoReconnect=true&rewriteBatchedStatements=true");
////        hikariConfig.setUsername("root");
////        hikariConfig.setPassword("zyh@123456");
//        hikariConfig.setDriverClassName("org.postgresql.Driver");
//        hikariConfig.setJdbcUrl(URL);
//        hikariConfig.setUsername(USER);
//        hikariConfig.setPassword(PASSWORD);
//        //设置可以获取tables remarks信息
////        hikariConfig.addDataSourceProperty("useInformationSchema", "true");
//        hikariConfig.setMinimumIdle(2);
//        hikariConfig.setMaximumPoolSize(5);
//        hikariConfig.setSchema("public");
//        DataSource dataSource = new HikariDataSource(hikariConfig);
//        //生成配置
//        EngineConfig engineConfig = EngineConfig.builder()
//            //生成文件路径
//            .fileOutputDir("E:\\")
//            //打开目录
//            .openOutputDir(true)
//            //文件类型
//            .fileType(EngineFileType.WORD)
//            //生成模板实现
//            .produceType(EngineTemplateType.freemarker).build();
//
//        //忽略表
//        ArrayList<String> ignoreTableName = new ArrayList<>();
//        ignoreTableName.add("test_user");
//        ignoreTableName.add("test_group");
//        //忽略表前缀
//        ArrayList<String> ignorePrefix = new ArrayList<>();
//        ignorePrefix.add("test_");
//        //忽略表后缀
//        ArrayList<String> ignoreSuffix = new ArrayList<>();
//        ignoreSuffix.add("_test");
//        ProcessConfig processConfig = ProcessConfig.builder()
//            //指定生成逻辑、当存在指定表、指定表前缀、指定表后缀时，将生成指定表，其余表不生成、并跳过忽略表配置
//            //根据名称指定表生成
//            .designatedTableName(new ArrayList<>())
//            //根据表前缀生成
//            .designatedTablePrefix(new ArrayList<>())
//            //根据表后缀生成
//            .designatedTableSuffix(new ArrayList<>())
//            //忽略表名
//            .ignoreTableName(ignoreTableName)
//            //忽略表前缀
//            .ignoreTablePrefix(ignorePrefix)
//            //忽略表后缀
//            .ignoreTableSuffix(ignoreSuffix).build();
//        //配置
//        Configuration config = Configuration.builder()
//            //版本
//            .version("1.0.0")
//            //描述
//            .description("数据库设计文档生成")
//            //数据源
//            .dataSource(dataSource)
//            //生成配置
//            .engineConfig(engineConfig)
//            //生成配置
//            .produceConfig(processConfig)
//            .build();
//        //执行生成
//        new DocumentationExecute(config).execute();




        // Step 1: Create HikariConfig object and set properties
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(URL);
        config.setUsername(USER);
        config.setPassword(PASSWORD);
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

        // Step 2: Create HikariDataSource object using the configuration
        HikariDataSource dataSource = new HikariDataSource(config);

        try (Connection connection = dataSource.getConnection()) {
            // Step 3: Get database metadata
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(null, "public", "%", new String[]{"TABLE"});

            // Step 4: Create a Word document
            XWPFDocument document = new XWPFDocument();
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                String tableComment = getTableComment(connection, "public", tableName);
                XWPFParagraph paragraph = document.createParagraph();
                XWPFRun run = paragraph.createRun();
                run.setText("表名: " + tableName+"("+tableComment+")");
                run.setBold(true);
                run.setFontSize(16);

                ResultSet columns = metaData.getColumns(null, "public", tableName, "%");
                XWPFTable table = document.createTable();
                XWPFTableRow headerRow = table.createRow();
                headerRow.createCell();
                headerRow.getCell(0).setText("字段名称");
                headerRow.createCell();
                headerRow.getCell(1).setText("类型");
                headerRow.createCell();
                headerRow.getCell(2).setText("是否可为空");
                headerRow.createCell();
                headerRow.getCell(3).setText("字段注释");


                while (columns.next()) {
                    XWPFTableRow row = table.createRow();
                    row.createCell();
                    row.getCell(0).setText(columns.getString("COLUMN_NAME"));
                    row.createCell();
                    row.getCell(1).setText(columns.getString("TYPE_NAME"));
                    row.createCell();
                    row.getCell(2).setText(columns.getString("IS_NULLABLE"));
                    row.createCell();
                    row.getCell(3).setText(getColumnComment(connection, "public", tableName, columns.getString("COLUMN_NAME")));
                }
            }

            // Step 5: Write the document to a file
            try (FileOutputStream out = new FileOutputStream("E:\\pgsql.docx")) {
                document.write(out);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dataSource != null) {
                dataSource.close();
            }
        }
    }

    private static String getTableComment(Connection connection, String schema, String tableName) throws SQLException {
        String query = "SELECT obj_description('\"" + schema + "\".\"" + tableName + "\"'::regclass) AS comment";
        try (Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(query)) {
            if (rs.next()) {
                return rs.getString("comment");
            } else {
                return "No comment";
            }
        }
    }

    private static String getColumnComment(Connection connection, String schema, String tableName, String columnName) throws SQLException {
        String query = "select column_comment from information_schema.tables t INNER JOIN information_schema.COLUMNS c \n" +
            "on  t.table_name = c.table_name\n" +
            "where c.table_name like '"+tableName+"' AND column_name = '"+columnName+"'";
        try (Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(query)) {
            if (rs.next()) {
                return rs.getString("column_comment");
            } else {
                return "No comment";
            }
        }
    }

    /**
     * 生成数据库文档
     * @return
     * @throws Exception
     */
    @GetMapping("/db/file")
    public R sendSmsCode() throws Exception {
        HikariConfig hikariConfig = new HikariConfig();
//        hikariConfig.setDriverClassName("com.mysql.cj.jdbc.Driver");
//        hikariConfig.setJdbcUrl("jdbc:mysql://172.16.5.90:13306/kws-commerce?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&autoReconnect=true&rewriteBatchedStatements=true");
//        hikariConfig.setUsername("root");
//        hikariConfig.setPassword("mysql_YmrW6y");
        hikariConfig.setJdbcUrl(URL);
        hikariConfig.setUsername(USER);
        hikariConfig.setPassword(PASSWORD);
        //设置可以获取tables remarks信息
        hikariConfig.addDataSourceProperty("useInformationSchema", "true");
        hikariConfig.setMinimumIdle(2);
        hikariConfig.setMaximumPoolSize(5);
//        hikariConfig.setSchema("public");
        DataSource dataSource = new HikariDataSource(hikariConfig);
        //生成配置
        EngineConfig engineConfig = EngineConfig.builder()
            //生成文件路径
            .fileOutputDir("E:\\")
            //打开目录
            .openOutputDir(true)
            //文件类型
            .fileType(EngineFileType.WORD)
            //生成模板实现
            .produceType(EngineTemplateType.freemarker).build();

        //忽略表
        ArrayList<String> ignoreTableName = new ArrayList<>();
        ignoreTableName.add("test_user");
        ignoreTableName.add("test_group");
        //忽略表前缀
        ArrayList<String> ignorePrefix = new ArrayList<>();
        ignorePrefix.add("test_");
        //忽略表后缀
        ArrayList<String> ignoreSuffix = new ArrayList<>();
        ignoreSuffix.add("_test");
        ProcessConfig processConfig = ProcessConfig.builder()
            //指定生成逻辑、当存在指定表、指定表前缀、指定表后缀时，将生成指定表，其余表不生成、并跳过忽略表配置
            //根据名称指定表生成
            .designatedTableName(new ArrayList<>())
            //根据表前缀生成
            .designatedTablePrefix(new ArrayList<>())
            //根据表后缀生成
            .designatedTableSuffix(new ArrayList<>())
            //忽略表名
            .ignoreTableName(ignoreTableName)
            //忽略表前缀
            .ignoreTablePrefix(ignorePrefix)
            //忽略表后缀
            .ignoreTableSuffix(ignoreSuffix).build();
        //配置
        Configuration config = Configuration.builder()
            //版本
            .version("1.0.0")
            //描述
            .description("数据库设计文档生成")
            //数据源
            .dataSource(dataSource)
            //生成配置
            .engineConfig(engineConfig)
            //生成配置
            .produceConfig(processConfig)
            .build();
        //执行生成
        new DocumentationExecute(config).execute();
        return R.ok();
    }

    /**
     * 获取设备UE对应关系数据
     * @return
     */
    @GetMapping("/device/ue")
    public R getDeviceUe() {
        JSONObject result = new JSONObject();
        //设备
        List<DicmDevice> deviceList = dicmDeviceService.getAllDeviceUe();
        for (DicmDevice dicmDevice : deviceList) {
            String prefix = dicmDevice.getUeId();
            if(dicmDevice.getDeviceName().indexOf("MDC1") > -1){
                prefix += "a";
            }else if(dicmDevice.getDeviceName().indexOf("MDC2") > -1){
                prefix += "b";
            }
            String key = (prefix+"_"+dicmDevice.getDeviceType()+"_"+dicmDevice.getDeviceName());
            String value = dicmDevice.getDeviceId();
            result.put(key,value);
        }

        //摄像头
        List<DicmNvr> nvrList = dicmNvrService.getAllDeviceUe();
        for (DicmNvr dicmNvr : nvrList) {
            String prefix = dicmNvr.getUeId();
            if(dicmNvr.getNvrName().indexOf("MDC01") > -1 || dicmNvr.getNvrName().indexOf("MDC02") > -1){
                prefix += "a";
            }else if(dicmNvr.getNvrName().indexOf("MDC03") > -1 || dicmNvr.getNvrName().indexOf("MDC04") > -1){
                prefix += "b";
            }
            String key = (prefix+"_1000_"+dicmNvr.getNvrName());
            String value = dicmNvr.getNvrId();
            result.put(key,value);
        }

        return R.ok(result);
    }

    /**
     * EVSAPI调用前置业务操作
     * @return
     */
    @GetMapping("/evs/before")
    public R evsBeforeHandle() {
        return R.ok(evsApiService.evsBeforeHandle());
    }

    /**
     * EVSAPI注册视频设备
     * @return
     */
    @PostMapping("/evs/device/register")
    public R evsDeviceRegister(@RequestBody DicmNvrRegisterDto dto) {
        return R.ok(evsApiService.evsDeviceRegister(dto));
    }

    @GetMapping("/evs/event")
    public R getEvsEvent() {
        return R.ok(evsApiService.getEvsEvent());
    }

}
